﻿using System;
using System.ComponentModel;

using System.Collections.Generic;

using System.Data;
using MySql.Data.MySqlClient;

using EagleJournalBE.Util;

namespace EagleJournalBE.Database
{
    public static class DatabaseConnector
    {
        private static MySqlConnection 
            databaseConnection;

        private static MySqlCommand 
            instCommand;
        private static MySqlCommand
            readCommand;
        private static MySqlCommand
            editCommand;
        private static MySqlCommand
            trshCommand;

        public  static void                 Connect             (string username, string password)
        {
            DoSQLQuery(new Action(() =>
                {
                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Connecting to MySQL server...");

                    databaseConnection.ConnectionString = string.Format("server={0};user={1};password={2};database=mydb", "localhost", username, password);
                    databaseConnection.Open();

                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Connection successful!");
                }), 3);
        }
        public  static void                 Disconnect          ()
        {
            DoSQLQuery(new Action(() =>
                {
                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Disconnecting from MySQL server...");
                    
                    databaseConnection.Close();

                    databaseConnection.Dispose();
                    databaseConnection = null;

                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Disconnection complete!");
                }), 3);
        }

        public  static void                 GetConnectorState   (out ConnectionState state)
        {
            state = databaseConnection.State;
        }

        public  static void                 InstQuery           (string table, Dictionary<string, object> data)
        {
            DoSQLQuery(new Action(() =>
                {
                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Inserting data into " + table + "...");

                    if (instCommand == null)
                    {
                        instCommand             = new MySqlCommand();
                        instCommand.Connection  = databaseConnection;
                    }

                    string buildDataString      = string.Empty;
                    string buildColumnString    = string.Empty;

                    foreach (KeyValuePair<string, object> cell in data)
                    {
                        string toSQLString = string.Empty;

                        if (cell.Value is string)
                            toSQLString = string.Format("\'{0}\'", cell.Value.ToString());
                        else
                            toSQLString = cell.Value.ToString();

                        buildDataString     += string.Format("{0}, ", toSQLString);
                        buildColumnString   += string.Format("{0}, ", cell.Key);
                    }
                    buildDataString     = buildDataString.Remove(buildDataString.Length - 2);
                    buildColumnString   = buildColumnString.Remove(buildColumnString.Length - 2);

                    instCommand.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, buildColumnString, buildDataString);
                    instCommand.ExecuteNonQuery();

                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Insertion complete!");
                }), 3);
        }
        public  static List<List<object>>   ReadQuery           (string table, string condition, List<string> columns)
        {
            List<List<object>> data = new List<List<object>>();

            DoSQLQuery(new Action(() =>
                {
                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Reading " + table + "'s contents...");

                    if (readCommand == null)
                    {
                        readCommand             = new MySqlCommand();
                        readCommand.Connection  = databaseConnection;
                    }

                    readCommand.CommandText = string.Format("SELECT * FROM {0}", table);
                    
                    if (!string.IsNullOrEmpty(condition)) 
                        readCommand.CommandText += string.Format(" WHERE {0}", condition);

                    using (MySqlDataReader reader = readCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            List<object> row = new List<object>();

                            for (int i = 0; i < columns.Count; i++)
                                row.Add(reader[columns[i]]);

                            data.Add(row);
                        } 
                    }

                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Read complete!");
                }), 3);

            return data;
        }
        public  static void                 EditQuery           (string table, string condition, Dictionary<string, object> row)
        {
            DoSQLQuery(new Action(() =>
                {
                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Altering table " + table + "...");

                    if (editCommand == null)
                    {
                        editCommand             = new MySqlCommand();
                        editCommand.Connection  = databaseConnection;
                    }

                    string buildString = string.Empty;

                    foreach (KeyValuePair<string, object> cell in row)
                    {
                        string toSQLString = string.Empty;

                        if (cell.Value is string)
                            toSQLString = string.Format("\'{0}\'", cell.Value.ToString());
                        else
                            toSQLString = cell.Value.ToString();

                        buildString += string.Format("{0} = {1}, ", cell.Key, toSQLString);
                    }
                    buildString = buildString.Remove(buildString.Length - 2);

                    editCommand.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", table, buildString, condition);
                    editCommand.ExecuteNonQuery();

                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Alteration complete!");
                }), 3);
        }
        public  static void                 TrshQuery           (string table, string condition)
        {
            DoSQLQuery(new Action(() =>
                {
                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Deleting data from table " + table + "...");

                    if (trshCommand == null)
                    {
                        trshCommand             = new MySqlCommand();
                        trshCommand.Connection  = databaseConnection;
                    }

                    trshCommand.CommandText = string.Format("DELETE FROM {0} WHERE {1}", table, condition);
                    trshCommand.ExecuteNonQuery();

                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Deletion complete!");
                }), 3);
        }

        private static void                 DoSQLQuery          (Action action, int retryCount)
        {
            if (databaseConnection == null)
                databaseConnection = new MySqlConnection();

            lock (databaseConnection)
            {
                try
                {
                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Executing MySQL command...");

                    action();

                    Logger.WriteInfo(LoggingType.INFO, databaseConnection, "Execution complete!");
                }
                catch (Exception mse)
                {
                    if (retryCount != 0)
                    {
                        Logger.WriteInfo(LoggingType.WARN, databaseConnection, "Error in executing MySQL action, " + retryCount + " attempt(s) remain.");

                        DoSQLQuery(action, retryCount - 1);
                    }
                    else
                    {
                        Logger.WriteInfo(LoggingType.STOP, databaseConnection, "Error in executing MySQL action after retries. Throwing exception.");

                        throw mse;
                    }
                }
            }
        }
    }
}
